# -*- coding: utf-8 -*-
import platform
import pymysql
import sys;
reload(sys);
sys.setdefaultencoding("utf8")

class MySqlUtils:
    def __init__(self):
        config = {
            'host': '127.0.0.1',
            'port': 3306,
            'user': 'root',
            'password': '123456',
            'db': 'hibullmarket',
            'charset': 'utf8mb4',
            'cursorclass': pymysql.cursors.DictCursor}
        if platform.system() == 'Linux':
            config['password'] = "37865016lZ"
            config['user'] = "root"
            config['db'] = "eastmoney"
        self.connection = pymysql.connect(**config)

    def insertToDb(self,tableName,bean):
        try:
            with self.connection.cursor() as cursor:
                columns = '('
                values = "("
                for k,v in bean.items():
                    columns = columns+'{k}'.format(k=k)+','
                    # str = '{k}'.format(k=k)
                    values = values+"'"+'{v}'.format(v=v)+"',"
                columns = columns.strip(',')+')'
                values = values.strip(',')+')'
                # print "coluumns:"
                sql =  "INSERT INTO "+tableName+" "+ columns  +" VALUES " + values
                print sql
                # data1 = pymysql.Binary(zlib.compress(detailImgsHref))
                # data2 = pymysql.Binary(zlib.compress(detailImgsLocal))
                cursor.execute(sql)
            # 没有设置默认自动提交，需要主动提交，以保存所执行的语句
                self.connection.commit()
        except Exception as e:
            print e
    def selectFromDb(self,tableName,columns,where):
        try:
            sql = "select "+columns+" from "+tableName+" "+where
            cursor = self.connection.cursor()
            cursor.execute(sql)
            print cursor.description
            # result = cursor.fetchall()
            # data_dict=[]
            # for field in cursor.description:
            #     data_dict.append(field[0])
            res = cursor.fetchall()
            # # 处理结果
            return res
            # print(data_dict)
        except Exception as e:
            print "MySqlUtils selectFromDb error"
            print e
    def select(self,sql):
        try:
            cursor = self.connection.cursor()
            cursor.execute(sql)
            print cursor.description
            # result = cursor.fetchall()
            # data_dict=[]
            # for field in cursor.description:
            #     data_dict.append(field[0])
            res = cursor.fetchall()
            # # 处理结果
            return res
            # print(data_dict)
        except Exception as e:
            print "MySqlUtils select error"
            print e
    def delete(self,sql):
        try:
            print "sql: "+sql
            cursor = self.connection.cursor()
            cursor.execute(sql)
            print cursor.description
            self.connection.commit()
            # print(data_dict)
        except Exception as e:
            print "MySqlUtils delete error"
            print e
    def updateTable(self,sql,param,imgYun,detailYun):
        try:
            with self.connection.cursor() as cursor:
                if param:
                    cursor.execute(sql,(param,imgYun,detailYun))
                else:
                    cursor.execute(sql, )
                self.connection.commit()
        except Exception as e:
            print "MySqlUtils update error"
            print e